﻿using DatabaseCore;
using DatabaseCore.Dao;
using DatabaseCore.Dao.DWH;
using Export.SAP.Service;
using Helper;
using Newtonsoft.Json;
using OfficeOpenXml;
using OGA.NPI.ICSH.ServiceBase.Entities;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.Security;
using System.Web.SessionState;
using SystemCore.Helper;
using NawaData.Dao;
using DatabaseCore.Dto;

namespace Export.Web.Ui.service
{
    /// <summary>
    /// Summary description for api
    /// </summary>
    public class api : IHttpHandler, IReadOnlySessionState
    {
        private OrderDao orderProvider = null;
        private OrderDetailDao orderDetailsProvider = null;
        private ProdQueueDao queueProvider = null;
        private RoleDao roleProvider = null;
        private UserDao userProvider = null;
        private ProductSpecialDao product = null;
        private TransactionDao transectionDao = null;
        private GiDao GiDao = null;

        public void ProcessRequest(HttpContext context)
        {
            HttpRequest post = context.Request;
            string ADAuthen = context.User.Identity.Name;
            string ip = HttpContext.Current.Request.UserHostAddress;
            string Medthod = post["metdhodName"];
            context.Response.ContentType = "text/plain";
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            string _username = HttpContext.Current.Session["Username"] == null ? "" : HttpContext.Current.Session["Username"].ToString();
            int _role = HttpContext.Current.Session["Role"] == null ? -1 : Convert.ToInt32(HttpContext.Current.Session["Role"]);

            if (!String.IsNullOrEmpty(Medthod))
            {

                switch (Medthod.Trim())
                {
                    #region Order Function
                    case "GetOrderAll":
                        string isExport = post["isExport"];

                        orderProvider = orderProvider ?? new OrderDao();
                        var orderAll = orderProvider.Search(null, 0, null, null, null, null, null, _role);

                        if (isExport == "true")
                        {
                            using (ExcelPackage pck = new ExcelPackage())
                            {
                                //Create the worksheet
                                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Order");
                                ws.Cells["A1"].LoadFromCollection(orderAll, true);
                                context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                context.Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
                                context.Response.BinaryWrite(pck.GetAsByteArray());
                                context.Response.End();
                            }
                        }

                        string orderAllSerialized = serializer.Serialize(orderAll);
                        context.Response.Write(orderAllSerialized);
                        context.Response.End();
                        break;
                    case "GetOrderDetailsExport":
                        string orderIdExport = post["orderId"];
                        if (string.IsNullOrEmpty(orderIdExport))
                        {
                            break;
                        }
                        else
                        {
                            orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                            var orderDetailsExport = orderDetailsProvider.SearchByOrderNoExport(orderIdExport);

                            using (ExcelPackage pck = new ExcelPackage())
                            {
                                //Create the worksheet
                                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("OrderDetails");
                                ws.Cells["A1"].LoadFromCollection(orderDetailsExport, true);
                                context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                context.Response.AddHeader("content-disposition", "attachment;  filename=OrderDetailDocNo" + orderIdExport + ".xlsx");
                                context.Response.BinaryWrite(pck.GetAsByteArray());
                                context.Response.End();
                            }
                            break;
                        }
                    case "GetOrderDetails":
                        string orderId = post["orderId"];
                        string orderStatus = post["status"];
                        if (string.IsNullOrEmpty(orderId))
                        {
                            break;
                        }
                        else
                        {
                            orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                            var orderDetails = orderDetailsProvider.SearchByOrderNo(orderId);
                            int defaultPercent = ConfigurationManager.AppSettings["defaultPercent"] == null ? 20 : Convert.ToInt32(ConfigurationManager.AppSettings["defaultPercent"]);
                            orderDetails.ToList().ForEach(ddUpdate => ddUpdate.DefaultPercent = defaultPercent);
                            if (!String.IsNullOrEmpty(orderStatus) && orderStatus == "1")
                            {
                                product = product ?? new ProductSpecialDao();
                                var specialPercent = product.GetAll(true);

                                foreach (var detail in orderDetails)
                                {
                                    var productSpecialPercent = specialPercent.Where(p => p.ProductCode.ToLower() == detail.MaterialNo.ToLower() && p.StartDate <= DateTime.Today && p.EndDate >= DateTime.Today).LastOrDefault();
                                    if (productSpecialPercent != null)
                                    {
                                        if (detail.Rate == null || detail.Rate == 0)
                                        {
                                            detail.Rate = productSpecialPercent.PercentSpecial;
                                        }
                                    }
                                    else
                                    {
                                        if (detail.Rate == null || detail.Rate == 0)
                                        {
                                            detail.Rate = defaultPercent;
                                        }
                                    }
                                }
                            }
                            string orderDetailsSerialized = serializer.Serialize(orderDetails);
                            context.Response.Write(orderDetailsSerialized);
                            context.Response.End();
                            break;
                        }


                    case "UpdateDocument":
                        string documents = post["postData[documents]"];
                        string documentDetails = post["postData[documentDetails]"];
                        var d = JsonConvert.DeserializeObject<tbl_Order>(documents);
                        var details = JsonConvert.DeserializeObject<List<tbl_OrderDetail>>(documentDetails);
                        orderProvider = orderProvider ?? new OrderDao();
                        orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                        switch (d.Status)
                        {
                            case 2:
                                d.SentToApproverTime = DateTime.Now;
                                break;
                            case 3:
                                d.ApprovedTime = DateTime.Now;
                                break;
                            case 5:
                                d.RejectedTime = DateTime.Now;
                                break;
                            default:
                                break;
                        }
                        orderProvider.Update(d);
                        orderDetailsProvider.Update(details, d.OrderNo, HttpContext.Current.User.Identity.Name);
                        break;

                    case "DeleteDocument":
                        string deleteOrderNo = post["postData[orderNo]"];
                        if (!string.IsNullOrEmpty(deleteOrderNo))
                        {
                            try
                            {
                                orderProvider = orderProvider ?? new OrderDao();
                                // also delete Queue and order detail
                                orderProvider.Delete(deleteOrderNo);

                            }
                            catch (Exception ex)
                            {
                                context.Response.Write(ex);
                                context.Response.End();
                            }
                            context.Response.Write("Delete Success");
                            context.Response.End();
                        }

                        break;

                    case "InsertDocumentDetail":
                        try
                        {
                            //var newDocDetail = JsonConvert.DeserializeObject<tbl_OrderDetail>(newDocDetailString);
                            string insertOrderDetailNo = post["postData[orderNo]"];
                            string insertOrderDetailMatNo = post["postData[matno]"];
                            string insertOrderDetailQty = post["postData[qty]"];

                            if (!string.IsNullOrEmpty(insertOrderDetailNo) && !string.IsNullOrEmpty(insertOrderDetailMatNo)
                                && !string.IsNullOrEmpty(insertOrderDetailQty))
                            {
                                var insertOrderDetail = new tbl_OrderDetail()
                                {
                                    OrderNo = insertOrderDetailNo,
                                    Qty = Convert.ToInt32(insertOrderDetailQty),
                                    ItemId = insertOrderDetailMatNo,
                                    MaterialNo = insertOrderDetailMatNo,
                                    CreateBy = _username,
                                    CreateDate = DateTime.Now,
                                    UpdateBy = _username,
                                    UpdateDate = DateTime.Now
                                };

                                orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                                // also delete Queue and order detail
                                orderDetailsProvider.Insert(insertOrderDetail);
                                break;
                            }

                        }
                        catch (Exception ex)
                        {
                            context.Response.Write(ex);
                            context.Response.End();
                            break;
                        }

                        break;

                    case "UpdateDocumentDetail":
                        string updateDocDetailString = post["postData[documentDetail]"];
                        if (!string.IsNullOrEmpty(updateDocDetailString))
                        {
                            try
                            {
                                var updateDocDetail = JsonConvert.DeserializeObject<tbl_OrderDetail>(updateDocDetailString);
                                orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                                // also delete Queue and order detail
                                orderDetailsProvider.Update(updateDocDetail);

                            }
                            catch (Exception ex)
                            {
                                context.Response.Write(ex);
                                context.Response.End();
                            }
                            context.Response.Write("Delete Success");
                            context.Response.End();
                        }

                        break;

                    case "DeleteDocumentDetail":
                        string deleteOrderDetailNo = post["postData[orderNo]"];
                        string deleteOrderDetailMatNo = post["postData[matNo]"];
                        if (!string.IsNullOrEmpty(deleteOrderDetailNo) && !string.IsNullOrEmpty(deleteOrderDetailMatNo))
                        {
                            try
                            {
                                orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                                // also delete Queue and order detail
                                orderDetailsProvider.Delete(deleteOrderDetailNo, deleteOrderDetailMatNo);

                            }
                            catch (Exception ex)
                            {
                                context.Response.Write(ex);
                                context.Response.End();
                            }
                            context.Response.Write("Delete Success");
                            context.Response.End();
                        }

                        break;
                    case "GetATP":
                        string prodIds = post["postData[prodIds]"];
                        var prodid = JsonConvert.DeserializeObject<OrderMats>(prodIds);
                        var productMovementHelper = new ProductMovement();

                        var products = new List<tbl_OrderDetail>();
                        foreach (var prodId in prodid.productIds)
                        {
                            products.Add(new tbl_OrderDetail() { MaterialNo = prodId, OrderNo = prodid.OrderId });
                        }

                        //context.Response.Write("kkk");
                        try
                        {
                            DataSet result = productMovementHelper.getATP(products, true);
                            string productAtpSerialized = JsonConvert.SerializeObject(result);
                            context.Response.Write(productAtpSerialized);
                        }
                        catch (Exception ex)
                        {

                            ExceptionHelper.LogAppError(ex.Message + "inner : " + ex.InnerException + " stactrck :" + ex.StackTrace);
                        }

                        context.Response.End();

                        break;

                    case "ApproveOrder":
                        try
                        {
                            context.Response.Clear();
                            string documentApprove = post["postData[documents]"];
                            string documentApproveDetails = post["postData[documentDetails]"];
                            var dApprove = JsonConvert.DeserializeObject<tbl_Order>(documentApprove);
                            var detailsApprove = JsonConvert.DeserializeObject<List<tbl_OrderDetail>>(documentApproveDetails);
                            orderProvider = orderProvider ?? new OrderDao();
                            transectionDao = transectionDao ?? new TransactionDao();
                            var productMovementApproveHelper = new ProductMovement();
                            ResponseGoodsMovementEntity resultSapMoved = null;
                            DataSet resultApproveOrder = productMovementApproveHelper.getATP(detailsApprove, false);
                            List<tbl_Transaction> Ts = new List<tbl_Transaction>();

                            for (int i = 0; i < resultApproveOrder.Tables.Count; i++)
                            {
                                foreach (var detail in detailsApprove)
                                {
                                    if (resultApproveOrder.Tables[i].TableName == detail.MaterialNo)
                                    {
                                        //make sure this mat do not moved to sap yet
                                        if (transectionDao.CheckisAlreadyMoveToSapOrderNoAndMatNo(detail.OrderNo, detail.MaterialNo) == false)
                                        {
                                            detail.ATP = Convert.ToInt32(Convert.ToDouble(resultApproveOrder.Tables[i].Rows[0].Field<String>("ATPQty")));
                                            var t = new tbl_Transaction();
                                            t.OrderNo = detail.OrderNo;
                                            t.MaterialNo = detail.MaterialNo;
                                            t.Plant = detail.Plant;
                                            t.Qty = detail.TransferQty;

                                            Ts.Add(t);
                                        }
                                    }
                                }
                            }

                            

                            resultSapMoved = productMovementApproveHelper.productMove(Ts);


                            //Todo need to verify the success from sap before add to Queue
                            var addToQueseComplete = new List<ProdQueue>();
                            if (resultSapMoved != null && resultSapMoved.Result)
                            {
                                //Prepare moved completed 
                                transectionDao = transectionDao ?? new TransactionDao();
                                List<tbl_Transaction> TsComplete = new List<tbl_Transaction>();
                                foreach (var item in Ts)
                                {
                                    item.Qty = item.Qty * -1;
                                    item.Result = resultSapMoved.MaterialDocumentNo;
                                    item.IsMoveCompleted = true;
                                    item.UpdateBy = "ATP";
                                    item.UpdateDate = DateTime.Now;
                                    TsComplete.Add(item);
                                }
                                //Add moved complete to transection
                                transectionDao.InsertCollection(TsComplete);

                                //Prepare the rest from atp to queue
                                foreach (var detail in detailsApprove)
                                {
                                    if (detail.Qty - detail.TransferQty > 0)
                                    {
                                        var q = new ProdQueue();
                                        q.ProductCode = detail.MaterialNo;
                                        q.PlantCode = detail.Plant;
                                        q.OrderNo = detail.OrderNo;
                                        q.Qty = detail.Qty - detail.TransferQty;
                                        q.Date = DateTime.Now;
                                        q.Rate = detail.Rate;
                                        q.ValidFrom = detail.ValidFrom;
                                        q.ValidTo = detail.ValidTo;
                                        q.IsActive = true;
                                        addToQueseComplete.Add(q);
                                    }
                                }

                                queueProvider = queueProvider ?? new ProdQueueDao();
                                //add to queue
                                var insertQuestResult = queueProvider.InsertCollection(addToQueseComplete);

                                //update order details
                                orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();

                                foreach (var o in detailsApprove)
                                {
                                    orderDetailsProvider.UpdateReceivedQty(o.OrderNo, o.ItemNo ?? 0, o.TransferQty, _username, o.ApproveQty, o.Plant);
                                }
                                if (orderProvider.CheckOrderIsClose(dApprove.OrderNo))
                                {
                                    orderProvider.UpdateOrderStatus(dApprove.OrderNo, 4, _username, true);
                                }
                                else
                                {
                                    orderProvider.UpdateOrderStatus(dApprove.OrderNo, 3, _username, true);
                                }
                            }
                            else
                            {

                                if (resultSapMoved != null)
                                {
                                    ExceptionHelper.LogAppError(resultSapMoved.MessageDescription);
                                    context.Response.Write("SAP service error : " + resultSapMoved.MessageDescription);
                                }
                                else
                                {
                                    //add all product to queue
                                    foreach (var detail in detailsApprove)
                                    {
                                        var q = new ProdQueue();
                                        q.ProductCode = detail.MaterialNo;
                                        q.PlantCode = detail.Plant;
                                        q.OrderNo = detail.OrderNo;
                                        q.Qty = detail.Qty;
                                        q.Date = DateTime.Now;
                                        q.Rate = detail.Rate;
                                        q.ValidFrom = detail.ValidFrom;
                                        q.ValidTo = detail.ValidTo;
                                        q.IsActive = true;
                                        addToQueseComplete.Add(q);
                                    }

                                    queueProvider = queueProvider ?? new ProdQueueDao();
                                    var insertQuestResult = queueProvider.InsertCollection(addToQueseComplete);
                                    //update order details
                                    orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();

                                    foreach (var o in detailsApprove)
                                    {
                                        orderDetailsProvider.UpdateReceivedQty(o.OrderNo, o.ItemNo ?? 0, o.TransferQty, _username, o.ApproveQty, o.Plant);
                                    }

                                    if (orderProvider.CheckOrderIsClose(dApprove.OrderNo))
                                    {
                                        orderProvider.UpdateOrderStatus(dApprove.OrderNo, 4, _username, true);
                                    }
                                    else
                                    {
                                        orderProvider.UpdateOrderStatus(dApprove.OrderNo, 3, _username, true);
                                    }
                                }

                                context.Response.Write("Success");
                                context.Response.End();
                                break;
                            }

                            //Save comment
                            List<Comment> csapproved = (from dd in detailsApprove
                                                select new Comment { OrderNo = dd.OrderNo, CommentText = dd.Comment, MatNo = dd.MaterialNo }).ToList();

                            //update order details
                            orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();

                            orderDetailsProvider.SaveComment(csapproved);
                            if (resultSapMoved != null)
                            {

                                context.Response.Write("Success(" + resultSapMoved.MaterialDocumentNo + ")");
                            }
                            else
                            {
                                context.Response.Write("Success");
                            }
                            context.Response.End();
                        }
                        catch (Exception ex)
                        {
                            ExceptionHelper.LogAppError(ex.Message + ": " + ex.StackTrace);
                            if (ex.InnerException != null)
                            {
                                ExceptionHelper.LogAppError(ex.InnerException.Message + ": " + ex.InnerException.StackTrace);
                            }

                            context.Response.Write(ex.Message);
                        }
                        

                        break;
                    #endregion

                    #region Queue Funciton

                    case "GetQueueAll":

                        queueProvider = queueProvider ?? new ProdQueueDao();
                        string order = post["order"];
                        string orderBy = post["sort"];
                        var queues = queueProvider.GetAllQueues();
                        foreach (var q in queues)
                        {
                            q.Rate = q.Rate ?? 20;
                        }
                        if (!string.IsNullOrEmpty(orderBy))
                        {
                            switch (orderBy)
                            {
                                case "ProductCode":
                                    if (order == "desc")
                                    {
                                        queues = queues.OrderByDescending(q => q.ProductCode).ToList();
                                    }
                                    else
                                    {
                                        queues = queues.OrderBy(q => q.ProductCode).ToList();
                                    }
                                    break;
                                case "OrderNo":
                                    if (order == "desc")
                                    {
                                        queues = queues.OrderByDescending(q => q.OrderNo).ToList();
                                    }
                                    else
                                    {
                                        queues = queues.OrderBy(q => q.OrderNo).ToList();
                                    }
                                    break;
                                case "Date":
                                    if (order == "desc")
                                    {
                                        queues = queues.OrderByDescending(q => q.Date).ToList();
                                    }
                                    else
                                    {
                                        queues = queues.OrderBy(q => q.Date).ToList();
                                    }
                                    break;
                                default:
                                    break;
                            }
                        }
                        string queuesSerialized = serializer.Serialize(queues);
                        context.Response.Write(queuesSerialized);
                        context.Response.End();
                        break;

                    case "UpdateQueueStatus":
                        string queueId = post["postData[queueId]"];
                        string queueStatus = post["postData[queueStatus]"];

                        if (string.IsNullOrEmpty(queueId) || string.IsNullOrEmpty(queueStatus))
                        {
                            break;
                        }

                        var qId = Convert.ToInt64(queueId);
                        var qStatus = Convert.ToBoolean(queueStatus);

                        queueProvider = queueProvider ?? new ProdQueueDao();
                        var qUpdateStatus = queueProvider.UpdateQueuePause(qId, _username, qStatus);
                        if (qUpdateStatus == false)
                        {
                            context.Response.Write("Queues are processing, please wait and try again");
                        }
                        else
                        {
                            context.Response.Write("Update Queue success");
                        }
                        context.Response.End();
                        break;

                    case "UpdateQueue":
                        string queueUpdate = post["postData[queue]"];

                        if (!string.IsNullOrEmpty(queueUpdate))
                        {
                            try
                            {
                                var queueUpdating = JsonConvert.DeserializeObject<List<ProdQueue>>(queueUpdate);
                                queueProvider = queueProvider ?? new ProdQueueDao();
                                orderProvider = orderProvider ?? new OrderDao();
                                // also delete Queue and order detail
                                var result = queueProvider.UpdateChangePlantAndQty(queueUpdating, _username);
                                if (result == false)
                                {
                                    context.Response.Write("Queues are processing, please wait and try again");
                                }
                                else
                                {
                                    var orderUpdate = queueUpdating.FirstOrDefault();

                                    if (orderUpdate != null && orderProvider.CheckOrderIsClose(orderUpdate.OrderNo))
                                    {
                                        orderProvider.UpdateOrderStatus(orderUpdate.OrderNo, 4, _username, true);
                                    }
                                    context.Response.Write("Update Queue success");
                                }
                            }
                            catch (Exception ex)
                            {
                                context.Response.Write(ex);
                                context.Response.End();
                            }
                        }

                        context.Response.End();
                        break;

                    case "DeleteQueue":
                        string queueDelete = post["postData[queue]"];

                        if (!string.IsNullOrEmpty(queueDelete))
                        {
                            try
                            {
                                var queueDeleting = JsonConvert.DeserializeObject<ProdQueue>(queueDelete);
                                queueProvider = queueProvider ?? new ProdQueueDao();
                                // also delete Queue and order detail
                                var result = queueProvider.Delete(queueDeleting.id, _username);
                                if (result == false)
                                {
                                    context.Response.Write("Queues are processing, please wait and try again");
                                }
                                else
                                {
                                    orderProvider = orderProvider ?? new OrderDao();
                                    if (orderProvider.CheckOrderIsClose(queueDeleting.OrderNo))
                                    {
                                        orderProvider.UpdateOrderStatus(queueDeleting.OrderNo, 4, _username, true);
                                    }
                                    context.Response.Write("Delete Queue success");
                                }
                            }
                            catch (Exception ex)
                            {
                                context.Response.Write(ex);
                                context.Response.End();
                            }
                        }

                        context.Response.End();
                        break;

                    case "GetTransectionByOrderNoAndMatExport":
                        string orderNotransectionExport = post["orderNo"];
                        string matNotransectionExport = post["matNo"];
                        transectionDao = transectionDao ?? new TransactionDao();
                        var transectionExport = transectionDao.GetTransectionByOrderNoAndMatNo(orderNotransectionExport, matNotransectionExport);
                        using (ExcelPackage pck = new ExcelPackage())
                        {
                            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Transactions");
                            ws.Cells["A1"].LoadFromCollection(transectionExport, true);
                            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            context.Response.AddHeader("content-disposition", "attachment;  filename=TransectionOnMat " + matNotransectionExport + ".xlsx");
                            context.Response.BinaryWrite(pck.GetAsByteArray());
                            context.Response.End();
                        }
                        break;
                    case "GetTransectionByOrderNoAndMat":
                        string orderNo = post["postData[orderNo]"];
                        string matNo = post["postData[matNo]"];
                        string isExportTransaction = post["isExport"];
                        transectionDao = transectionDao ?? new TransactionDao();
                        var transection = transectionDao.GetTransectionByOrderNoAndMatNo(orderNo, matNo);

                        string transectionSerialized = JsonConvert.SerializeObject(transection);
                        context.Response.Write(transectionSerialized);
                        context.Response.End();

                        break;

                    case "GetTransectionDetail":
                        string dateFrom = post["postData[dateFrom]"];
                        string dateTo = post["postData[dateTo]"];
                        string plantSrc = post["postData[plantSrc]"];
                        string plantDest = post["postData[plantDest]"];
                        string orderId2 = post["postData[orderId]"];
                        string productId = post["postData[productId]"];
                        string poId = post["postData[poId]"];
                        string moveType = post["postData[moveType]"];
                        string status = post["postData[status]"];
                        string issueBy = post["postData[issueBy]"];

                        DateTime DateFrom = DateTime.MinValue;
                        DateTime DateTo = DateTime.MinValue;
                        Int32 Status = -1;
                        if (!string.IsNullOrEmpty(dateFrom))
                        {
                            DateTime.TryParse(dateFrom, out DateFrom);
                        }

                        if (!string.IsNullOrEmpty(dateTo))
                        {
                            DateTime.TryParse(dateTo, out DateTo);
                        }

                        if (!string.IsNullOrEmpty(status))
                        {
                            Int32.TryParse(status, out Status);
                        }

                        transectionDao = transectionDao ?? new TransactionDao();
                        var transectionDetailWithOrder = transectionDao.GetTransectionDetails(DateFrom, DateTo, plantSrc, plantDest, orderId2, productId,
                            poId, moveType, Status, issueBy).Take(200);
                        string transectionDetailSerialized = serializer.Serialize(transectionDetailWithOrder);
                        context.Response.Write(transectionDetailSerialized);
                        context.Response.End();
                        break;

                    #endregion

                    #region User Fucntion

                    case "getAllRole":

                        roleProvider = roleProvider ?? new RoleDao();
                        var allRoles = roleProvider.GetAllRole();
                        string rolesSerialized = serializer.Serialize(allRoles);
                        context.Response.Write(rolesSerialized);
                        context.Response.End();

                        break;

                    case "getAllRoleActive":

                        roleProvider = roleProvider ?? new RoleDao();
                        var allRolesActive = roleProvider.GetAllRoleActive();
                        string rolesActiveSerialized = serializer.Serialize(allRolesActive);
                        context.Response.Write(rolesActiveSerialized);
                        context.Response.End();

                        break;

                    case "getAllUser":

                        userProvider = userProvider ?? new UserDao();
                        var users = userProvider.GetAllUser();
                        string usersSerialized = JsonConvert.SerializeObject(users);
                        context.Response.Write(usersSerialized);
                        context.Response.End();

                        break;

                    case "login":

                        string username = post["username"];
                        string password = post["password"];
                        userProvider = userProvider ?? new UserDao();
                        var usersLogin = userProvider.Login(username, password);

                        string userLoggedSerialized = serializer.Serialize(usersLogin);
                        // Add the cookie to the request to save it
                        HttpCookie cookie = new HttpCookie("exportCookie", userLoggedSerialized);
                        context.Response.Cookies.Add(cookie);

                        context.Response.Write(userLoggedSerialized);
                        context.Response.End();

                        break;

                    case "addUser":
                        userProvider = userProvider ?? new UserDao();
                        string addUsername = post["data[0][username]"];
                        string addPassword = post["data[0][password]"];
                        string addEmail = post["data[0][email]"];
                        string addFullname = post["data[0][fullname]"];
                        string addRole = post["data[0][userRole]"];
                        string addPhone = post["data[0][phone]"];
                        string addFax = post["data[0][fax]"];
                        string addPinCode = post["data[0][pincode]"];
                        string addActive = post["data[0][isactive]"];
                        var newUser = new tbl_User()
                        {
                            UserName = addUsername,
                            Password = addPassword,
                            Email = addEmail,
                            FullName = addFullname,
                            Role = string.IsNullOrEmpty(addRole) ? 2 : int.Parse(addRole),
                            Telephone = addPhone,
                            Fax = addFax,
                            PinCode = addPinCode,
                            Active = string.IsNullOrEmpty(addActive) ? true : bool.Parse(addActive)

                        };
                        userProvider.Insert(newUser);
                        context.Response.Write("Add user success.");
                        context.Response.End();

                        break;

                    case "editUser":
                        userProvider = userProvider ?? new UserDao();
                        string editUsername = post["data[0][username]"];
                        string editPassword = post["data[0][password]"];
                        string editEmail = post["data[0][email]"];
                        string editFullname = post["data[0][fullname]"];
                        string editRole = post["data[0][userRole]"];
                        string editPhone = post["data[0][phone]"];
                        string editFax = post["data[0][fax]"];
                        string editPinCode = post["data[0][pincode]"];
                        string editActive = post["data[0][isactive]"];
                        var editUser = new tbl_User()
                        {
                            UserName = editUsername,
                            Password = editPassword,
                            Email = editEmail,
                            FullName = editFullname,
                            Role = string.IsNullOrEmpty(editRole) ? 2 : int.Parse(editRole),
                            Telephone = editPhone,
                            Fax = editFax,
                            PinCode = editPinCode,
                            Active = string.IsNullOrEmpty(editActive) ? true : bool.Parse(editActive)
                        };

                        userProvider.Update(editUser);
                        context.Response.Write("Edit user success.");
                        context.Response.End();

                        break;

                    case "deleteUser":
                        userProvider = userProvider ?? new UserDao();
                        string usernameDelete = post["username"];
                        if (!string.IsNullOrEmpty(usernameDelete))
                        {
                            userProvider.Delete(usernameDelete);
                        }
                        context.Response.Write("Delete user success.");
                        context.Response.End();

                        break;

                    #region Role
                    case "addRole":
                        roleProvider = roleProvider ?? new RoleDao();
                        string roleName = post["rolename"];
                        string addRoleActive = post["isactive"];

                        if (!string.IsNullOrEmpty(roleName))
                        {
                            roleProvider.Insert(new tbl_Role()
                            {
                                Name = roleName,
                                IsActive = !string.IsNullOrEmpty(addRoleActive) ? bool.Parse(addRoleActive) : true
                            });
                        }
                        context.Response.Write("Add role success.");
                        context.Response.End();

                        break;

                    case "editRole":
                        roleProvider = roleProvider ?? new RoleDao();
                        string editroleName = post["rolename"];
                        string editRoleActive = post["isactive"];

                        if (!string.IsNullOrEmpty(editroleName))
                        {
                            roleProvider.Update(new tbl_Role()
                            {
                                Name = editroleName,
                                IsActive = !string.IsNullOrEmpty(editRoleActive) ? bool.Parse(editRoleActive) : true
                            });
                        }
                        context.Response.Write("Edit role success.");
                        context.Response.End();

                        break;

                    case "deleteRole":
                        roleProvider = roleProvider ?? new RoleDao();
                        string roleid = post["roleid"];
                        if (!string.IsNullOrEmpty(roleid))
                        {
                            roleProvider.Delete(int.Parse(roleid));
                        }
                        context.Response.Write("Delete role success.");
                        context.Response.End();

                        break;


                    case "getAllSpecialPercent":
                        product = product ?? new ProductSpecialDao();

                        var allProduct = product.GetAll();
                        var allProductDwhPercent = new DwhHelper().GetVmsa140_product_basic(null, null, null).AsEnumerable();
                        var all = from s in allProduct
                                  join p in allProductDwhPercent on s.ProductCode.Trim() equals p.Field<string>("mmatno").ToString().Trim()
                                  select new
                                  {
                                      Id = s.Id,
                                      ProductCode = s.ProductCode,
                                      PercentSpecial = s.PercentSpecial,
                                      StartDate = s.StartDate,
                                      EndDate = s.EndDate,
                                      IsActive = s.IsActive,
                                      ProductName = p.Field<string>("mmatthai").ToString()
                                  };

                        string allProductSerialized = serializer.Serialize(all);
                        context.Response.Write(allProductSerialized);
                        context.Response.End();

                        break;
                    case "getAllProduct":
                        var productNo = post["productNo"];
                        var productName = post["productName"];
                        var productNameEng = post["productNameEng"];
                        var dwh = new DwhHelper();
                        var allProductDwh = dwh.GetVmsa140_product_basic(productNo.ToLower(), productName.ToLower(), productNameEng.ToLower());

                        string allProductDwhSerialized = JsonConvert.SerializeObject(allProductDwh);
                        context.Response.Write(allProductDwhSerialized);
                        context.Response.End();

                        break;
                    case "deleteSpecialPercent":
                        product = product ?? new ProductSpecialDao();
                        string ProductCodeDelete = post["producId"];
                        if (!string.IsNullOrEmpty(ProductCodeDelete))
                        {
                            bool result = product.Delete(int.Parse(ProductCodeDelete));
                            if (result)
                            {
                                context.Response.Write("delete success");
                                context.Response.End();
                            }
                            else
                            {
                                context.Response.Write("can't delete ");
                                context.Response.End();
                            }
                        }
                        break;

                    case "saveProductSpecial":
                        product = product ?? new ProductSpecialDao();
                        string ProductCode = post["data[0][productNo]"];
                        string PercentSpecial = post["data[0][specialPercent]"];
                        string StartDate = post["data[0][startdate]"];
                        string EndDate = post["data[0][enddate]"];
                        string IsActive = post["data[0][isactive]"];

                        string isInsert = post["data[0][IsInsert]"];
                        string Id = post["data[0][Id]"];

                        var ProductSpecial = new ProductSpecialPercent()
                        {
                            ProductCode = ProductCode,
                            PercentSpecial = string.IsNullOrEmpty(PercentSpecial) ? 20 : int.Parse(PercentSpecial),
                            StartDate = string.IsNullOrEmpty(StartDate) ? DateTime.Now : DateTime.Parse(StartDate),
                            EndDate = string.IsNullOrEmpty(EndDate) ? DateTime.Now.AddDays(7) : DateTime.Parse(EndDate),
                            IsActive = string.IsNullOrEmpty(IsActive) ? true : bool.Parse(IsActive)

                        };
                        if (!string.IsNullOrEmpty(Id))
                        {
                            ProductSpecial.Id = int.Parse(Id);
                        }

                        if (isInsert == "true")
                        {
                            product.Insert(ProductSpecial);
                        }
                        else
                        {
                            product.Update(ProductSpecial);
                        }
                        context.Response.Write("Save product success.");
                        context.Response.End();

                        break;

                    case "getOrderConfirm":
                        orderProvider = orderProvider ?? new OrderDao();
                        string confirmOrderDetailNo = post["orderNo"];
                        string refOrder = orderProvider.GetRefOrder(confirmOrderDetailNo);
                        string confirmOrderDetailMatNo = post["matNo"];
                        GiDao = GiDao ?? new GiDao();
                        var confirmOrderByDocMatResult = GiDao.getGiByOrderMatNo(refOrder, confirmOrderDetailMatNo);

                        string confirmOrderByDocMat = JsonConvert.SerializeObject(confirmOrderByDocMatResult);
                        context.Response.Write(confirmOrderByDocMat);
                        context.Response.End();

                        break;

                    case "sendforgotPassword":
                        string forgotUserName = post["username"];
                        userProvider = userProvider ?? new UserDao();
                        tbl_User u = userProvider.GetUserByUserName(forgotUserName);
                        if (u != null)
                        {
                            string email = u.Email;
                            string error = string.Empty;
                            if (!string.IsNullOrEmpty(email))
                            {
                                Email.SendMail("exportSystem@scg.co.th", email, "แจ้งรหัสผ่านระบบ export", "รหัสผ่านของคุณคือ " + u.Password, false, out error);
                                context.Response.Write("ระบบส่ง password ไปที่ " + email + " แล้ว");
                                context.Response.End();
                            }
                            else
                            {
                                context.Response.Write("User ไม่ได้ระบุ email ไว้กรุณาติดต่อ admin.");
                                context.Response.End();
                            }
                            if (!string.IsNullOrEmpty(error))
                            {
                                ExceptionHelper.LogAppError(error);
                                context.Response.Write("ไม่สามารถส่ง email ได้กรุณาติดต่อ admin.");
                                context.Response.End();
                            }
                        }
                        else
                        {
                            context.Response.Write("Username ไม่ถูกต้อง");
                            context.Response.End();
                        }

                        break;

                    case "UpdatePassword":
                        string PasswordUpdate = post["password"];
                        userProvider = userProvider ?? new UserDao();
                        bool resultUpdatePassword = userProvider.UpdatePassword(_username, PasswordUpdate);
                        context.Response.Write(resultUpdatePassword.ToString().ToLower());
                        context.Response.End();
                        break;

                    case "ExportBackOrder":

                        orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                        var backorderList = orderDetailsProvider.GetBackOrder();


                        using (ExcelPackage pck = new ExcelPackage())
                        {
                            //Create the worksheet
                            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Order");
                            ws.Cells["A1"].LoadFromCollection(backorderList, true);
                            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            context.Response.AddHeader("content-disposition", "attachment;  filename=BackOrder.xlsx");
                            context.Response.BinaryWrite(pck.GetAsByteArray());
                            context.Response.End();
                        }
                       
                        //string orderAllSerialized = serializer.Serialize(orderAll);
                        //context.Response.Write(orderAllSerialized);
                        context.Response.End();
                        break;

                    case "SaveComment":

                        orderDetailsProvider = orderDetailsProvider ?? new OrderDetailDao();
                        string savecomment = post["postData[savecomment]"];

                        var documentDetail = JsonConvert.DeserializeObject<List<tbl_OrderDetail>>(savecomment);
                        List<Comment> cs = (from dd in documentDetail
                                           select new Comment { OrderNo = dd.OrderNo, CommentText = dd.Comment, MatNo = dd.MaterialNo }).ToList();


                        orderDetailsProvider.SaveComment(cs);

                        //string orderAllSerialized = serializer.Serialize(orderAll);
                        context.Response.Write(true);
                        context.Response.End();
                        break;
                    #endregion
                    #endregion

                }
            }
        }


        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}